#!/bin/sh

CUR_PATH=$(cd "$(dirname "$0")"; pwd)
source ${CUR_PATH}/mysql_server.conf
curr_date=$1
curr_year=date -d ${curr_date} +%Y
next_year=$((curr_year+1))
suffix=date -d "${curr_date}" +%y
#echo $curr_date
#echo $curr_year
#echo $next_year
#echo $suffix

create_table_sql=$(cat <<EOF

DROP TABLE IF EXISTS torque_ranges_info_day_${suffix};

CREATE TABLE `torque_ranges_info_day_${suffix}` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '表主键',
  `uid` bigint(20) NOT NULL COMMENT '唯一ID',
  `tid` bigint(20) DEFAULT NULL COMMENT '通讯号',
  `torque0_duration` int(11) DEFAULT NULL COMMENT '扭矩=0时长，单位：秒',
  `torque10_duration` int(11) DEFAULT NULL COMMENT '扭矩0<x<=10时长，单位：秒',
  `torque20_duration` int(11) DEFAULT NULL COMMENT '扭矩10<x<=20时长，单位：秒',
  `torque30_duration` int(11) DEFAULT NULL COMMENT '扭矩20<x<=30时长，单位：秒',
  `torque40_duration` int(11) DEFAULT NULL COMMENT '扭矩30<x<=40时长，单位：秒',
  `torque50_duration` int(11) DEFAULT NULL COMMENT '扭矩40<x<=50时长，单位：秒',
  `torque60_duration` int(11) DEFAULT NULL COMMENT '扭矩50<x<=60时长，单位：秒',
  `torque70_duration` int(11) DEFAULT NULL COMMENT '扭矩60<x<=70时长，单位：秒',
  `torque80_duration` int(11) DEFAULT NULL COMMENT '扭矩70<x<=80时长，单位：秒',
  `torque90_duration` int(11) DEFAULT NULL COMMENT '扭矩80<x<=90时长，单位：秒',
  `torque99_duration` int(11) DEFAULT NULL COMMENT '扭矩90<x<100时长，单位：秒',
  `torque100_duration` int(11) DEFAULT NULL COMMENT '扭矩=100时长，单位：秒',
  `max_torque` int(11) DEFAULT NULL COMMENT '最高扭矩',
  `min_torque` int(11) DEFAULT NULL COMMENT '最低扭矩',
  `torque_sum` int(11) DEFAULT NULL COMMENT '扭矩总和',
  `torque_count` int(11) DEFAULT NULL COMMENT '扭矩总个数',
  `avg_torque` int(11) DEFAULT NULL COMMENT '平均负荷，速度>0 转速大于0，扭矩总和/扭矩个数',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `day` int(11) NOT NULL COMMENT 'yyyyMMdd 数据时间',
  PRIMARY KEY (`id`,`day`),
  UNIQUE KEY `uid_day_index` (`uid`,`day`) USING BTREE,
  KEY `day` (`day`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100000038 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT
 PARTITION BY RANGE (day)
(PARTITION p${curr_year}0101 VALUES LESS THAN (${curr_year}0201) ENGINE = InnoDB,
 PARTITION p${curr_year}0201 VALUES LESS THAN (${curr_year}0301) ENGINE = InnoDB,
 PARTITION p${curr_year}0301 VALUES LESS THAN (${curr_year}0401) ENGINE = InnoDB,
 PARTITION p${curr_year}0401 VALUES LESS THAN (${curr_year}0501) ENGINE = InnoDB,
 PARTITION p${curr_year}0501 VALUES LESS THAN (${curr_year}0601) ENGINE = InnoDB,
 PARTITION p${curr_year}0601 VALUES LESS THAN (${curr_year}0701) ENGINE = InnoDB,
 PARTITION p${curr_year}0701 VALUES LESS THAN (${curr_year}0801) ENGINE = InnoDB,
 PARTITION p${curr_year}0801 VALUES LESS THAN (${curr_year}0901) ENGINE = InnoDB,
 PARTITION p${curr_year}0901 VALUES LESS THAN (${curr_year}1001) ENGINE = InnoDB,
 PARTITION p${curr_year}1001 VALUES LESS THAN (${curr_year}1101) ENGINE = InnoDB,
 PARTITION p${curr_year}1101 VALUES LESS THAN (${curr_year}1201) ENGINE = InnoDB,
 PARTITION p${curr_year}1201 VALUES LESS THAN (${next_year}0101) ENGINE = InnoDB) ;



CREATE TABLE `torque_ranges_info_week_${suffix}` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '表主键',
  `uid` bigint(20) NOT NULL COMMENT '唯一ID',
  `tid` bigint(20) DEFAULT NULL COMMENT '通讯号',
  `torque0_duration` int(11) DEFAULT NULL COMMENT '扭矩=0时长，单位：秒',
  `torque10_duration` int(11) DEFAULT NULL COMMENT '扭矩0<x<=10时长，单位：秒',
  `torque20_duration` int(11) DEFAULT NULL COMMENT '扭矩10<x<=20时长，单位：秒',
  `torque30_duration` int(11) DEFAULT NULL COMMENT '扭矩20<x<=30时长，单位：秒',
  `torque40_duration` int(11) DEFAULT NULL COMMENT '扭矩30<x<=40时长，单位：秒',
  `torque50_duration` int(11) DEFAULT NULL COMMENT '扭矩40<x<=50时长，单位：秒',
  `torque60_duration` int(11) DEFAULT NULL COMMENT '扭矩50<x<=60时长，单位：秒',
  `torque70_duration` int(11) DEFAULT NULL COMMENT '扭矩60<x<=70时长，单位：秒',
  `torque80_duration` int(11) DEFAULT NULL COMMENT '扭矩70<x<=80时长，单位：秒',
  `torque90_duration` int(11) DEFAULT NULL COMMENT '扭矩80<x<=90时长，单位：秒',
  `torque99_duration` int(11) DEFAULT NULL COMMENT '扭矩90<x<100时长，单位：秒',
  `torque100_duration` int(11) DEFAULT NULL COMMENT '扭矩=100时长，单位：秒',
  `max_torque` int(11) DEFAULT NULL COMMENT '最高扭矩',
  `min_torque` int(11) DEFAULT NULL COMMENT '最低扭矩',
  `torque_sum` int(11) DEFAULT NULL COMMENT '扭矩总和',
  `torque_count` int(11) DEFAULT NULL COMMENT '扭矩总个数',
  `avg_torque` int(11) DEFAULT NULL COMMENT '平均负荷，速度>0 转速大于0，扭矩总和/扭矩个数',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `day` int(11) NOT NULL COMMENT 'yyyyMMdd 数据时间',
  PRIMARY KEY (`id`,`day`),
  UNIQUE KEY `uid_day_index` (`uid`,`day`) USING BTREE,
  KEY `day` (`day`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100000038 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT
 PARTITION BY RANGE (day)
(PARTITION p${curr_year}0101 VALUES LESS THAN (${curr_year}0201) ENGINE = InnoDB,
 PARTITION p${curr_year}0201 VALUES LESS THAN (${curr_year}0301) ENGINE = InnoDB,
 PARTITION p${curr_year}0301 VALUES LESS THAN (${curr_year}0401) ENGINE = InnoDB,
 PARTITION p${curr_year}0401 VALUES LESS THAN (${curr_year}0501) ENGINE = InnoDB,
 PARTITION p${curr_year}0501 VALUES LESS THAN (${curr_year}0601) ENGINE = InnoDB,
 PARTITION p${curr_year}0601 VALUES LESS THAN (${curr_year}0701) ENGINE = InnoDB,
 PARTITION p${curr_year}0701 VALUES LESS THAN (${curr_year}0801) ENGINE = InnoDB,
 PARTITION p${curr_year}0801 VALUES LESS THAN (${curr_year}0901) ENGINE = InnoDB,
 PARTITION p${curr_year}0901 VALUES LESS THAN (${curr_year}1001) ENGINE = InnoDB,
 PARTITION p${curr_year}1001 VALUES LESS THAN (${curr_year}1101) ENGINE = InnoDB,
 PARTITION p${curr_year}1101 VALUES LESS THAN (${curr_year}1201) ENGINE = InnoDB,
 PARTITION p${curr_year}1201 VALUES LESS THAN (${next_year}0101) ENGINE = InnoDB) ;



CREATE TABLE `torque_ranges_info_month_${suffix}` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '表主键',
  `uid` bigint(20) NOT NULL COMMENT '唯一ID',
  `tid` bigint(20) DEFAULT NULL COMMENT '通讯号',
  `torque0_duration` int(11) DEFAULT NULL COMMENT '扭矩=0时长，单位：秒',
  `torque10_duration` int(11) DEFAULT NULL COMMENT '扭矩0<x<=10时长，单位：秒',
  `torque20_duration` int(11) DEFAULT NULL COMMENT '扭矩10<x<=20时长，单位：秒',
  `torque30_duration` int(11) DEFAULT NULL COMMENT '扭矩20<x<=30时长，单位：秒',
  `torque40_duration` int(11) DEFAULT NULL COMMENT '扭矩30<x<=40时长，单位：秒',
  `torque50_duration` int(11) DEFAULT NULL COMMENT '扭矩40<x<=50时长，单位：秒',
  `torque60_duration` int(11) DEFAULT NULL COMMENT '扭矩50<x<=60时长，单位：秒',
  `torque70_duration` int(11) DEFAULT NULL COMMENT '扭矩60<x<=70时长，单位：秒',
  `torque80_duration` int(11) DEFAULT NULL COMMENT '扭矩70<x<=80时长，单位：秒',
  `torque90_duration` int(11) DEFAULT NULL COMMENT '扭矩80<x<=90时长，单位：秒',
  `torque99_duration` int(11) DEFAULT NULL COMMENT '扭矩90<x<100时长，单位：秒',
  `torque100_duration` int(11) DEFAULT NULL COMMENT '扭矩=100时长，单位：秒',
  `max_torque` int(11) DEFAULT NULL COMMENT '最高扭矩',
  `min_torque` int(11) DEFAULT NULL COMMENT '最低扭矩',
  `torque_sum` int(11) DEFAULT NULL COMMENT '扭矩总和',
  `torque_count` int(11) DEFAULT NULL COMMENT '扭矩总个数',
  `avg_torque` int(11) DEFAULT NULL COMMENT '平均负荷，速度>0 转速大于0，扭矩总和/扭矩个数',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `day` int(11) NOT NULL COMMENT 'yyyyMMdd 数据时间',
  PRIMARY KEY (`id`,`day`),
  UNIQUE KEY `uid_day_index` (`uid`,`day`) USING BTREE,
  KEY `day` (`day`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100000038 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT
 PARTITION BY RANGE (day)
(PARTITION p${curr_year}0101 VALUES LESS THAN (${curr_year}0201) ENGINE = InnoDB,
 PARTITION p${curr_year}0201 VALUES LESS THAN (${curr_year}0301) ENGINE = InnoDB,
 PARTITION p${curr_year}0301 VALUES LESS THAN (${curr_year}0401) ENGINE = InnoDB,
 PARTITION p${curr_year}0401 VALUES LESS THAN (${curr_year}0501) ENGINE = InnoDB,
 PARTITION p${curr_year}0501 VALUES LESS THAN (${curr_year}0601) ENGINE = InnoDB,
 PARTITION p${curr_year}0601 VALUES LESS THAN (${curr_year}0701) ENGINE = InnoDB,
 PARTITION p${curr_year}0701 VALUES LESS THAN (${curr_year}0801) ENGINE = InnoDB,
 PARTITION p${curr_year}0801 VALUES LESS THAN (${curr_year}0901) ENGINE = InnoDB,
 PARTITION p${curr_year}0901 VALUES LESS THAN (${curr_year}1001) ENGINE = InnoDB,
 PARTITION p${curr_year}1001 VALUES LESS THAN (${curr_year}1101) ENGINE = InnoDB,
 PARTITION p${curr_year}1101 VALUES LESS THAN (${curr_year}1201) ENGINE = InnoDB,
 PARTITION p${curr_year}1201 VALUES LESS THAN (${next_year}0101) ENGINE = InnoDB) ;



EOF
)
#echo ${create_table_sql}
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"
if [ $? -ne 0 ] ; then
  echo "表创建失败"
else
  echo "表创建成功"
fi
